package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.swing.JOptionPane;
import javax.swing.text.html.parser.Entity;

import constant.Constant;
import entity.Parameters;
import entity.TableNameAndType;

public class DBUtils {

    public static Connection getConnection(Parameters parameters) {

        Connection conn = null;

        String url = "";

        String driverClass = "";

        if ("mysql".equals(parameters.getDataBaseTypeVal())) {
            try {

                url = "jdbc:mysql://" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + "/"
                        + parameters.getDataBaseNameVal()
                        + "?connectTimeout=10000&socketTimeout=10000&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull";
                driverClass = "com.mysql.jdbc.Driver";

                parameters.setDataBaseUrl(url);

                parameters.setDataBaseDriverClass(driverClass);

                Class.forName(driverClass);

                DriverManager.setLoginTimeout(10);

                conn = DriverManager.getConnection(url, parameters.getDataBaseUserNameVal(),
                        parameters.getDataBasePwdVal());


            } catch (Exception e) {
                JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！", "错误",
                        JOptionPane.ERROR_MESSAGE);
                return null;
            }
        } else if ("oracle".equals(parameters.getDataBaseTypeVal())) {

            try {

                url = "jdbc:oracle:thin:@//" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + "/"
                        + parameters.getDataBaseNameVal();
                driverClass = "oracle.jdbc.driver.OracleDriver";

                parameters.setDataBaseUrl(url);

                parameters.setDataBaseDriverClass(driverClass);

                Class.forName(driverClass);

                Properties info = new Properties();
                info.put("oracle.net.CONNECT_TIMEOUT", 10000);
                info.put("oracle.jdbc.ReadTimeout", 10000);
                info.put("user", parameters.getDataBaseUserNameVal());
                info.put("password", parameters.getDataBasePwdVal());
                DriverManager.setLoginTimeout(10);
                conn = DriverManager.getConnection(url, info);

            } catch (Exception e) {

                JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！", "错误",
                        JOptionPane.ERROR_MESSAGE);
                return null;

            }
        } else if ("postgresql".equals(parameters.getDataBaseTypeVal())) {

            try {

                url = "jdbc:postgresql://" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal()
                        + "/" + parameters.getDataBaseNameVal() + "?socketTimeout=1&connectTimeout=1&useUnicode=true&characterEncoding=UTF-8";

                driverClass = "org.postgresql.Driver";

                parameters.setDataBaseUrl(url);

                parameters.setDataBaseDriverClass(driverClass);

                Class.forName(driverClass);

                DriverManager.setLoginTimeout(10);

                conn = DriverManager.getConnection(url, parameters.getDataBaseUserNameVal(),
                        parameters.getDataBasePwdVal());

            } catch (Exception e) {

                JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！", "错误",
                        JOptionPane.ERROR_MESSAGE);
                return null;
            }
        }

        return conn;
    }

    /**
     * 目前仅限mysql和postgresql
     *
     * @param tableNameVal
     * @param connection
     * @return
     */
    private static Map<String, String> getColumnComment(String databaseType, String tableNameVal, Connection connection) {

        PreparedStatement columnListPst = null;
        ResultSet columnListRs = null;

        String sql = "";
        if ("mysql".equals(databaseType)) {
            sql = "show full columns from `" + tableNameVal + "`";
        } else if ("postgresql".equals(databaseType)) {

            String[] tableNameArr = tableNameVal.split("\\.");

            if (tableNameArr.length == 1) {
                tableNameVal = tableNameArr[0];
            } else {
                tableNameVal = tableNameArr[1];
            }

            sql = "SELECT" +
                    " A.attname AS \"Field\"," +
                    " col_description ( A.attrelid, A.attnum ) AS \"Comment\"" +
                    " FROM" +
                    " pg_class AS C," +
                    " pg_attribute AS A " +
                    " WHERE" +
                    " C.relname = '" + tableNameVal + "' " +
                    " AND A.attrelid = C.oid " +
                    " AND A.attnum > 0";
        }

        // 列名集合
        Map<String, String> commentMqp = new HashMap<>();
        try {
            columnListPst = connection.prepareStatement(sql);
            columnListRs = columnListPst.executeQuery();

            while (columnListRs.next()) {

                commentMqp.put(columnListRs.getString("Field").toUpperCase(), columnListRs.getString("Comment"));

            }

        } catch (SQLException e) {
            return null;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (columnListPst != null) {
                    columnListPst.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (columnListRs != null) {
                    columnListRs.close();
                }
            } catch (SQLException e) {

            }
        }
        return commentMqp;
    }

    public static List<String> getColumnNameList(String databaseType, String tableNameVal, Connection connection) {

        PreparedStatement columnListPst = null;
        ResultSet columnListRs = null;

        String sql = "";
        if ("mysql".equals(databaseType)) {
            sql = "select * from `" + tableNameVal + "` where 1=0";
        } else if ("oracle".equals(databaseType)) {
            sql = "select * from \"" + tableNameVal + "\" where 1=0";
        } else if ("postgresql".equals(databaseType)) {
            sql = "select * from " + tableNameVal + " where 1=0";
        }

        // 列名集合
        List<String> columnList = new ArrayList<>();

        try {
            columnListPst = connection.prepareStatement(sql);
            columnListRs = columnListPst.executeQuery();
            ResultSetMetaData metaData = columnListRs.getMetaData();

            int columnCount = metaData.getColumnCount();

            for (int i = 0; i < columnCount; i++) {
                columnList.add(metaData.getColumnName(i + 1));
            }

        } catch (SQLException e) {
            return null;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (columnListPst != null) {
                    columnListPst.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (columnListRs != null) {
                    columnListRs.close();
                }
            } catch (SQLException e) {

            }
        }
        return columnList;
    }


    /**
     * 获取表中所有字段名称和类型
     *
     * @return
     */
    public static List<TableNameAndType> getColumnNameAndTypes(String databaseType, String tableNameVal,
                                                               Connection connection) {

        PreparedStatement columnListPst = null;
        ResultSet columnListRs = null;

        String sql = "";
        if ("mysql".equals(databaseType)) {
            sql = "select * from `" + tableNameVal + "` where 1=0";
        } else if ("oracle".equals(databaseType)) {
            sql = "select * from \"" + tableNameVal + "\" where 1=0";
        } else if ("postgresql".equals(databaseType)) {
            sql = "select * from " + tableNameVal + " where 1=0";
        }

        // 列名集合
        List<TableNameAndType> nameAndTypes = new ArrayList<>();

        try {
            columnListPst = connection.prepareStatement(sql);
            columnListRs = columnListPst.executeQuery();
            ResultSetMetaData metaData = columnListRs.getMetaData();

            int columnCount = metaData.getColumnCount();

            for (int i = 0; i < columnCount; i++) {
                TableNameAndType nameAndType = new TableNameAndType();
                nameAndType.setName(metaData.getColumnName(i + 1));

                //设置sqlParamName
                nameAndType.setSqlParamName(DataUtils.getSqlParam(metaData.getColumnName(i + 1)));

                nameAndType.setComment((metaData.getColumnName(i + 1)));

                String javaTypeName = "";
                String javaClassName = "";
                String columnTypeName = metaData.getColumnTypeName(i + 1).toUpperCase();

                switch (databaseType) {
                    case "postgresql":
                        switch (columnTypeName) {
                            case "VARCHAR":
                                javaTypeName = "String";
                                break;
                            case "CHAR":
                                javaTypeName = "String";
                                break;
                            case "INT2":
                                javaTypeName = "Integer";
                                break;
                            case "INT4":
                                javaTypeName = "Integer";
                                break;
                            case "INT8":
                                javaTypeName = "Long";
                                break;
                            case "SERIAL2":
                                javaTypeName = "Integer";
                                break;
                            case "SERIAL4":
                                javaTypeName = "Integer";
                                break;
                            case "SERIAL8":
                                javaTypeName = "Long";
                                break;
                            case "TEXT":
                                javaTypeName = "String";
                                break;
                            case "DATE":
                                javaTypeName = "Date";
                                javaClassName = "java.sql.Date";
                                break;
                            case "TIME":
                                javaTypeName = "Time";
                                javaClassName = "java.sql.Time";
                                break;
                            case "TIMETZ":
                                javaTypeName = "Time";
                                javaClassName = "java.sql.Time";
                                break;
                            case "TIMESTAMP":
                                javaTypeName = "Timestamp";
                                javaClassName = " java.sql.Timestamp";
                                break;
                            case "TIMESTAMPTZ":
                                javaTypeName = "Timestamp";
                                javaClassName = " java.sql.Timestamp";
                                break;
                            default:
                                javaTypeName = "String";
                                break;
                        }
                        break;
                    case "mysql":
                        switch (columnTypeName) {
                            case "VARCHAR":
                                javaTypeName = "String";
                                break;
                            case "INT":
                                javaTypeName = "Integer";
                                break;
                            case "CHAR":
                                javaTypeName = "String";
                                break;
                            case "BLOB":
                                javaTypeName = "byte[]";
                                break;
                            case "TEXT":
                                javaTypeName = "String";
                                break;
                            case "INTEGER":
                                javaTypeName = "Long";
                                break;
                            case "TINYINT":
                                javaTypeName = "Integer";
                                break;
                            case "SMALLINT":
                                javaTypeName = "Integer";
                                break;
                            case "MEDIUMINT":
                                javaTypeName = "Integer";
                                break;
                            case "BIT":
                                javaTypeName = "Integer";
                                break;
                            case "BIGINT":
                                javaTypeName = "BigInteger";
                                javaClassName = "java.math.BigInteger";
                                break;
                            case "FLOAT":
                                javaTypeName = "Float";
                                break;
                            case "DOUBLE":
                                javaTypeName = "Double";
                                break;
                            case "DECIMAL":
                                javaTypeName = "BigDecimal";
                                javaClassName = "java.math.BigDecimal";
                                break;
                            case "BOOLEAN":
                                javaTypeName = "Integer";
                                break;
                            // 主键
                            case "ID":
                                javaTypeName = "Long";
                                break;
                            case "DATE":
                                javaTypeName = "Date";
                                javaClassName = "java.sql.Date";
                                break;
                            case "TIME":
                                javaTypeName = "Time";
                                javaClassName = "java.sql.Time";
                                break;
                            case "DATETIME":
                                javaTypeName = "Timestamp";
                                javaClassName = " java.sql.Timestamp";
                                break;
                            case "TIMESTAMP":
                                javaTypeName = "Timestamp";
                                javaClassName = " java.sql.Timestamp";
                                break;
                            case "YEAR":
                                javaTypeName = "Date";
                                javaClassName = "java.sql.Date";
                                break;
                            default:
                                javaTypeName = "String";
                                break;
                        }
                        break;
                    case "oracle":
                        switch (columnTypeName) {
                            case "NUMBER":
                                javaTypeName = "FLOAT";
                                break;
                            case "DATE":
                                javaTypeName = "Date";
                                javaClassName = "java.sql.Date";
                                break;
                            case "TIMESTAMP":
                                javaTypeName = "Timestamp";
                                javaClassName = " java.sql.Timestamp";
                                break;
                            default:
                                javaTypeName = "String";
                                break;
                        }
                    default:
                        break;
                }

                nameAndType.setTypeName(javaTypeName);
                nameAndType.setClassName(javaClassName);

                nameAndTypes.add(nameAndType);
            }

            //System.out.println(nameAndTypes);

            if ("mysql".equals(databaseType) || "postgresql".equals(databaseType)) {

                // 注释map
                Map<String, String> columnComment = getColumnComment(databaseType, tableNameVal, connection);

                for (TableNameAndType tableNameAndType : nameAndTypes) {

                    String name = tableNameAndType.getName().toUpperCase();

                    // 设置注释内容
                    tableNameAndType.setComment(
                            "".equals(columnComment.get(name)) || columnComment.get(name) == null ? tableNameAndType.getName() : columnComment.get(name));

                }
            }

        } catch (SQLException e) {
            return null;
        } finally {
            try {

                if (connection != null) {
                    connection.close();
                }

            } catch (SQLException e) {
            }
            try {
                if (columnListPst != null) {
                    columnListPst.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (columnListRs != null) {
                    columnListRs.close();
                }
            } catch (SQLException e) {

            }
        }
        return nameAndTypes;
    }

}
